﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using TreeGrid;

namespace arrbomBOM
{
    public partial class Form1 : Form
    {
		private string connectionString = "server=localhost;database=cf_mes_db;uid=root;password=123456;";
		MySQLHelper mySQLHelper;
		private string logPath1;

		public Form1()
        {
            InitializeComponent();

			mySQLHelper = new MySQLHelper(connectionString);

		}
        string filePath = "";
        private void button1_Click(object sender, EventArgs e)
        {
			logPath1 = this.txtSoundPath.Text;
			//创建文件弹出选择窗口（包括文件名）对象
			OpenFileDialog ofd = new OpenFileDialog();
			ofd.InitialDirectory = logPath1;
			//判断选择的路径
			if (ofd.ShowDialog() == DialogResult.OK)
            {
                this.txtSoundPath.Text = ofd.FileName.ToString();
            }
            filePath = this.txtSoundPath.Text;
		}

		private void button2_Click(object sender, EventArgs e)
		{
			if (string.IsNullOrEmpty(filePath))
			{
				filePath = this.txtSoundPath.Text;
			}
			mySQLHelper.Truncate("arrbom");
			DataTable dt = NPOIClass.ExcelToDataTable(filePath);

			DataView dv = dt.DefaultView;
			DataTable dataTable = dv.ToTable(true, "分段");
			for (int i = 0; i < dataTable.Rows.Count; i++)
			{
				Dictionary<string, object> data = new Dictionary<string, object>();
				data.Add("segmentation", dataTable.Rows[i]["分段"].ToString());
				data.Add("PartNumber", dataTable.Rows[i]["分段"].ToString());
				int result = mySQLHelper.Insert("arrbom", data);
			}

			for(int i = 0; i < dt.Rows.Count; i++)
            {
				Dictionary<string, object> data = new Dictionary<string, object>();
				data.Add("item", dt.Rows[i][0].ToString());
				data.Add("SerialNumber", dt.Rows[i][1].ToString());
				data.Add("type", dt.Rows[i][2].ToString());
				data.Add("segmentation", dt.Rows[i][3].ToString());
				data.Add("assemble", dt.Rows[i][4].ToString());
				data.Add("PartNumber", dt.Rows[i][5].ToString());
				data.Add("process", dt.Rows[i][6].ToString());
				data.Add("polish", dt.Rows[i][7].ToString());
				data.Add("flowdirection1", dt.Rows[i][8].ToString());
				data.Add("flowdirection2", dt.Rows[i][9].ToString());
				data.Add("Plate_FlatSteel", dt.Rows[i][10].ToString());
				data.Add("FBstripmaterial", dt.Rows[i][11].ToString());
				data.Add("FBstripmaterial2", dt.Rows[i][12].ToString());
				data.Add("ProfileSpecifications", dt.Rows[i][13].ToString());
				data.Add("ProfileLength", dt.Rows[i][14].ToString());
				data.Add("material", dt.Rows[i][15].ToString());
				data.Add("CuttingBoardDrawingName", dt.Rows[i][16].ToString());
				data.Add("weight", dt.Rows[i][17].ToString());
				string strAssembling = dt.Rows[i][18].ToString();
				data.Add("Assembling", strAssembling);
				data.Add("AreaCM", dt.Rows[i][19].ToString());
				data.Add("AreaM", dt.Rows[i][20].ToString());
				data.Add("CuttingLength", dt.Rows[i][21].ToString());
				string[] splitA = strAssembling.Split('/');
				for(int j = 0; j < splitA.Length; j++)
                {
					data.Add("Assembling"+(j+1), splitA[j]);
				}

				//if (splitA.Length == 2)
    //            {

				//		DataTable dtSelect = mySQLHelper.ExecuteQuery("SELECT * FROM arrbom where Assembling1='"+ splitA[0]+ "' and Assembling2 is null");
				//	if(dtSelect.Rows.Count>0)
    //                {

    //                }
				//}
				int result = mySQLHelper.Insert("arrbom", data);
			}
			//整理数据库
			ArrbomData();
		}

		private void ArrbomData()
		{
            List<TreeGridViewRow> lstRow = new List<TreeGridViewRow>();
			DataTable dtSelect = mySQLHelper.ExecuteQuery("SELECT * FROM arrbom where Assembling1 is null");
			for (int i = 0; i < dtSelect.Rows.Count; i++)
            {
                TreeGridViewRow row = new TreeGridViewRow();
                row.CreateCells(treeGridView1);
                string text = string.Format("{0}", i + 1);
				row.Cells[0].Value = text;
				row.Cells[1].Value = dtSelect.Rows[i][7].ToString();
				row.Cells[2].Value = dtSelect.Rows[i][10].ToString();
				row.Cells[3].Value = dtSelect.Rows[i][11].ToString();
				row.Cells[4].Value = dtSelect.Rows[i][20].ToString();

				lstRow.Add(row);

                DataGridViewRow[] rows = BuildSubRows(row, dtSelect.Rows[i][0].ToString(), dtSelect.Rows[i][7].ToString());


                    row.IsExpand = false;
            }

            treeGridView1.Rows.AddRange(lstRow.ToArray());
        }

		private DataGridViewRow[] BuildSubRows(TreeGridViewRow row,string pid,string partNumber)
		{
			List<DataGridViewRow> lstRow = new List<DataGridViewRow>();

			Dictionary<string, object> data = new Dictionary<string, object>();
			data.Add("pid", pid);
			mySQLHelper.Update("arrbom", data, "Assembling1 ='" + partNumber + "' and Assembling2 is not null and Assembling3 is null");

			//插入第三的值
			DataTable dtSelectSub = mySQLHelper.ExecuteQuery("SELECT * FROM arrbom where Assembling1 ='" + partNumber + "' and Assembling2 is not null and Assembling3 is not null and Assembling4 is null");
			DataView dv = dtSelectSub.DefaultView;
			DataTable dataTable = dv.ToTable(true, "Assembling");
			for (int i = 0; i < dataTable.Rows.Count; i++)
			{
				string Assembling = dataTable.Rows[i]["Assembling"].ToString();
				string[] ass = Assembling.Split('/');
				if (ass.Length == 3)
				{
					Dictionary<string, object> dataSub = new Dictionary<string, object>();
					dataSub.Add("pid", pid);
					dataSub.Add("segmentation", ass[0]);
					dataSub.Add("Assembling1", ass[0]);
					dataSub.Add("Assembling2", ass[1]);
					dataSub.Add("PartNumber", ass[2]);
					int result = mySQLHelper.Insert("arrbom", dataSub);
				}
			}


			DataTable dtSelect = mySQLHelper.ExecuteQuery("SELECT * FROM arrbom where Assembling1 ='"+ partNumber + "' and Assembling2 is not null and Assembling3 is null");
			for (int i = 0; i < dtSelect.Rows.Count; i++)
			{
				TreeGridViewRow subRow = new TreeGridViewRow();
				row.AddChildRow(subRow);
				subRow.CreateCells(treeGridView1);

				string text = string.Format("{0}-{1}", row.Cells[0].Value, i + 1);
				subRow.Cells[0].Value = text;
				subRow.Cells[1].Value = dtSelect.Rows[i][7].ToString();
				subRow.Cells[2].Value = dtSelect.Rows[i][10].ToString();
				subRow.Cells[3].Value = dtSelect.Rows[i][11].ToString();
				subRow.Cells[4].Value = dtSelect.Rows[i][20].ToString();

                if (string.IsNullOrEmpty(dtSelect.Rows[i][20].ToString())){
                    //if (i % 3 == 0)
                        BuildSubSubRows(subRow, dtSelect.Rows[i][0].ToString(), dtSelect.Rows[i][7].ToString(), partNumber);
                }

				subRow.IsExpand = false;
				lstRow.Add(subRow);
			}
			


			return lstRow.ToArray();
		}

		private DataGridViewRow[] BuildSubSubRows(TreeGridViewRow row, string pid, string partNumber,string dingPart)
		{
			List<DataGridViewRow> lstRow = new List<DataGridViewRow>();

			Dictionary<string, object> data = new Dictionary<string, object>();
			data.Add("pid", pid);
			mySQLHelper.Update("arrbom", data, "Assembling1 ='"+ dingPart + "' and Assembling2 is not null and Assembling3 ='"+ partNumber + "' and Assembling4 is null");

            //插入第四的值
            DataTable dtSelectSub = mySQLHelper.ExecuteQuery("SELECT * FROM arrbom where Assembling1 ='" + dingPart + "' and Assembling2 is not null and Assembling3 ='"+ partNumber + "' and Assembling4 is not null and Assembling5 is null");

            DataView dv = dtSelectSub.DefaultView;
			DataTable dataTable = dv.ToTable(true, "Assembling");
			for (int i = 0; i < dataTable.Rows.Count; i++)
			{
				string Assembling = dataTable.Rows[i]["Assembling"].ToString();
				string[] ass = Assembling.Split('/');
				if (ass.Length == 4)
				{
					Dictionary<string, object> dataSub = new Dictionary<string, object>();
					dataSub.Add("pid", pid);
					dataSub.Add("segmentation", ass[0]);
					dataSub.Add("Assembling1", ass[0]);
					dataSub.Add("Assembling2", ass[1]);
					dataSub.Add("Assembling3", ass[2]);
					dataSub.Add("PartNumber", ass[3]);
					int result = mySQLHelper.Insert("arrbom", dataSub);
				}
			}


			DataTable dtSelect = mySQLHelper.ExecuteQuery("SELECT * FROM arrbom where Assembling1 ='" + dingPart + "' and Assembling2 is not null and Assembling3 ='" + partNumber + "' and Assembling4 is null");
			for (int i = 0; i < dtSelect.Rows.Count; i++)
			{
				TreeGridViewRow subRow = new TreeGridViewRow();
				row.AddChildRow(subRow);
				subRow.CreateCells(treeGridView1);

				string text = string.Format("{0}-{1}", row.Cells[0].Value, i + 1);
				subRow.Cells[0].Value = text;
				subRow.Cells[1].Value = dtSelect.Rows[i][7].ToString();
				subRow.Cells[2].Value = dtSelect.Rows[i][10].ToString();
				subRow.Cells[3].Value = dtSelect.Rows[i][11].ToString();
				subRow.Cells[4].Value = dtSelect.Rows[i][20].ToString();

				if (string.IsNullOrEmpty(dtSelect.Rows[i][20].ToString()))
				{
					BuildSub4Rows(subRow, dtSelect.Rows[i][0].ToString(), dtSelect.Rows[i][7].ToString(), dingPart, partNumber);
				}

				subRow.IsExpand = false;
				lstRow.Add(subRow);
			}			

			return lstRow.ToArray();
		}

		private DataGridViewRow[] BuildSub4Rows(TreeGridViewRow row, string pid, string partNumber, string dingPart1, string dingPart3)
		{
			List<DataGridViewRow> lstRow = new List<DataGridViewRow>();

			Dictionary<string, object> data = new Dictionary<string, object>();
			data.Add("pid", pid);
			mySQLHelper.Update("arrbom", data, "Assembling1 ='" + dingPart1 + "' and Assembling2 is not null and Assembling3 ='" + dingPart3 + "'and Assembling4 ='" + partNumber + "' and Assembling5 is null");

			//插入第四的值
			DataTable dtSelectSub = mySQLHelper.ExecuteQuery("SELECT * FROM arrbom where Assembling1 ='" + dingPart1 + "' and Assembling2 is not null and Assembling3 ='" + dingPart3 + "'and Assembling4 ='" + partNumber + "' and Assembling5 is not null");

			DataView dv = dtSelectSub.DefaultView;
			DataTable dataTable = dv.ToTable(true, "Assembling");
			for (int i = 0; i < dataTable.Rows.Count; i++)
			{
				string Assembling = dataTable.Rows[i]["Assembling"].ToString();
				string[] ass = Assembling.Split('/');
				if (ass.Length == 5)
				{
					Dictionary<string, object> dataSub = new Dictionary<string, object>();
					dataSub.Add("pid", pid);
					dataSub.Add("segmentation", ass[0]);
					dataSub.Add("Assembling1", ass[0]);
					dataSub.Add("Assembling2", ass[1]);
					dataSub.Add("Assembling3", ass[2]);
					dataSub.Add("Assembling4", ass[3]);
					dataSub.Add("PartNumber", ass[4]);
					int result = mySQLHelper.Insert("arrbom", dataSub);
				}
			}


			DataTable dtSelect = mySQLHelper.ExecuteQuery("SELECT * FROM arrbom where Assembling1 ='" + dingPart1 + "' and Assembling2 is not null  and Assembling3 ='" + dingPart3 + "'and Assembling4 ='" + partNumber + "' and Assembling5 is null");
			for (int i = 0; i < dtSelect.Rows.Count; i++)
			{
				TreeGridViewRow subRow = new TreeGridViewRow();
				row.AddChildRow(subRow);
				subRow.CreateCells(treeGridView1);

				string text = string.Format("{0}-{1}", row.Cells[0].Value, i + 1);
				subRow.Cells[0].Value = text;
				subRow.Cells[1].Value = dtSelect.Rows[i][7].ToString();
				subRow.Cells[2].Value = dtSelect.Rows[i][10].ToString();
				subRow.Cells[3].Value = dtSelect.Rows[i][11].ToString();
				subRow.Cells[4].Value = dtSelect.Rows[i][20].ToString();

				if (string.IsNullOrEmpty(dtSelect.Rows[i][20].ToString()))
				{
					//BuildSub4Rows(subRow, dtSelect.Rows[i][0].ToString(), dtSelect.Rows[i][7].ToString(), dingPart, partNumber);
				}

				subRow.IsExpand = false;
				lstRow.Add(subRow);
			}

			return lstRow.ToArray();
		}

    }
}
